import pandas as pd
from clickhouse_driver import Client
from datetime import datetime, timedelta
from tabulate import tabulate
from datetime import date
import logging
import time
from mysql_proxy import MySQLSSHClient
# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class CompareChannelData:
    def __init__(self):
        self.client = MySQLSSHClient()



    def get_mysql_cpa_user(self, dayNum):

        # 方法1: 常规使用a.date
        if self.client.connect():
            try:
                sql=f"""
                WITH new_data AS (
                    SELECT DATE,SUM(dau) AS dau_new,SUM(wau) AS wau_new,SUM(mau) AS mau_new,SUM(dnu) AS dnu_new,SUM(uv) AS uv_new FROM cfx_t.data_cpa_user_dau_2 
                    WHERE DATE>=UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL {dayNum} DAY))*1000 GROUP BY DATE
                ),
                old_data AS (
                    SELECT DATE,SUM(dau) AS dau_old,SUM(wau) AS wau_old,SUM(mau) AS mau_old,SUM(dnu) AS dnu_old,SUM(uv) AS uv_old FROM cfx_t.data_valid_user_dau
                    WHERE DATE>=UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL {dayNum} DAY))*1000 GROUP BY DATE
                )
                SELECT FROM_UNIXTIME(a.date / 1000, '%Y-%m-%d') as date,a.dau_new,b.dau_old,(a.dau_new-b.dau_old) AS dau_diff,ROUND((a.dau_new-b.dau_old)/a.dau_new*100,2) AS dau_per,
                a.wau_new,b.wau_old,(a.wau_new-b.wau_old) AS wau_diff,ROUND((a.wau_new-b.wau_old)/a.wau_new*100,2) AS wau_per,
                a.mau_new,b.mau_old,(a.mau_new-b.mau_old) AS mau_diff,ROUND((a.mau_new-b.mau_old)/a.mau_new*100,2) AS mau_per,
                a.dnu_new,b.dnu_old,(a.dnu_new-b.dnu_old) AS dnu_diff,ROUND((a.dnu_new-b.dnu_old)/a.dnu_new*100,2) AS dnu_per,
                a.uv_new,b.uv_old,(a.uv_new-b.uv_old) AS uv_diff,ROUND((a.uv_new-b.uv_old)/a.uv_new*100,2) AS uv_per
                FROM new_data AS a
                LEFT JOIN old_data AS b
                ON a.date=b.date
                ORDER BY a.date DESC
                """
                data, columns = self.client.query(sql)
                # logger.info(f"MySQL cpa_user数据: {data}")
                # logger.info(f"MySQL cpa_user数据 columns: {columns}")
                return data, columns
            finally:
                self.client.close()

    def get_mysql_cpa_channel(self, dayNum):

        # 方法1: 常规使用a.date
        if self.client.connect():
            try:
                sql=f"""
                WITH new_data AS (
                SELECT DATE,`channel`,SUM(dau) AS dau_new,SUM(wau) AS wau_new,SUM(mau) AS mau_new,SUM(dnu) AS dnu_new,SUM(uv) AS uv_new FROM cfx_t.data_cpa_user_channel_dau_2 
                WHERE DATE>=UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL {dayNum} DAY))*1000 AND CHANNEL='DK68M1C002' GROUP BY DATE
                ),
                old_data AS (
                SELECT DATE,`channel`,SUM(dau) AS dau_old,SUM(wau) AS wau_old,SUM(mau) AS mau_old,SUM(dnu) AS dnu_old,SUM(uv) AS uv_old FROM cfx_t.data_valid_user_channel_dau
                WHERE DATE>=UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL {dayNum} DAY))*1000 AND CHANNEL='DK68M1C002' GROUP BY DATE
                )
                SELECT FROM_UNIXTIME(a.date / 1000, '%Y-%m-%d') AS DATE,a.channel,a.dau_new,b.dau_old,(a.dau_new-b.dau_old) AS dau_diff,ROUND((a.dau_new-b.dau_old)/a.dau_new*100,2) AS dau_per,
                a.wau_new,b.wau_old,(a.wau_new-b.wau_old) AS wau_diff,ROUND((a.wau_new-b.wau_old)/a.wau_new*100,2) AS wau_per,
                a.mau_new,b.mau_old,(a.mau_new-b.mau_old) AS mau_diff,ROUND((a.mau_new-b.mau_old)/a.mau_new*100,2) AS mau_per,
                a.dnu_new,b.dnu_old,(a.dnu_new-b.dnu_old) AS dnu_diff,ROUND((a.dnu_new-b.dnu_old)/a.dnu_new*100,2) AS dnu_per,
                a.uv_new,b.uv_old,(a.uv_new-b.uv_old) AS uv_diff,ROUND((a.uv_new-b.uv_old)/a.uv_new*100,2) AS uv_per
                FROM new_data AS a
                LEFT JOIN old_data AS b
                ON a.date=b.date
                ORDER BY a.date DESC
                """
                data, columns = self.client.query(sql)
                # logger.info(f"MySQL cpa_user数据: {data}")
                # logger.info(f"MySQL cpa_user数据 columns: {columns}")
                return data, columns
            finally:
                self.client.close()

    def display_date(self, headers,data):
        """展示同一日期不同维度的对比数据"""
        
        # 示例数据 - 同一日期的不同维度数据
        current_date = date.today().strftime("%Y-%m-%d")
        
        # 数据结构: [维度名称, 数值1, 数值2, 数值3, 变化率(%)]
        # data = [
        #     ["销售额", 15600, 14200, 13500, 15.56],
        #     ["访问量", 3240, 2890, 2650, 22.26],
        #     ["转化率", 4.8, 4.2, 3.9, 23.08],
        #     ["客单价", 286, 275, 260, 10.00],
        #     ["新用户数", 245, 198, 180, 36.11]
        # ]
        
        # 表格标题
        #print(f"===== {current_date} 各维度数据对比表 =====")
        # print("(数值1:今日, 数值2:昨日, 数值3:前日, 变化率:今日较前日)")
        # print()
        
        # 定义表头
        #headers = ["维度", "数值1", "数值2", "数值3", "变化率(%)"]
        
        # 打印表格
        print(tabulate(data, headers=headers, tablefmt="grid"))
        
        # 打印数据说明
        # print("\n数据说明:")
        # print("1. 变化率计算公式: (数值1 - 数值3) / 数值3 * 100%")
        # print("2. 正数表示增长，负数表示下降")

    def print_2d_array_center(self, data, width=12):
        """
        使用 center() 方法打印二维数组，内容居中对齐
        """
        for row in data:
            for item in row:
                # 将内容转换为字符串，然后居中对齐
                print(f"|{str(item).center(width)}|", end='')
            print()  # 换行

    

# 使用示例
if __name__ == "__main__":
    # 创建连接实例
    compare_obj = CompareChannelData()

    dayNum=5

    data,columns = compare_obj.get_mysql_cpa_user(dayNum)
    display_data = []
    display_data.append(columns)
    for row in data:
        day_data=[]
        for key in row:
            day_data.append(row[key])
        display_data.append(day_data)

    print(f"===== 最近{dayNum}日总体数据对比 =====")
    compare_obj.print_2d_array_center(display_data)

    data,columns = compare_obj.get_mysql_cpa_channel(dayNum)
    display_data = []
    display_data.append(columns)
    for row in data:
        day_data=[]
        for key in row:
            day_data.append(row[key])
        display_data.append(day_data)

    print(f"===== 最近{dayNum}日DK68M1C002渠道数据对比 =====")
    compare_obj.print_2d_array_center(display_data)
    print("\n数据说明:")
    print("1. 差值: new - old")
    print("2. 比例(%)计算公式: 差值 / new * 100%")

